//APAGAR BANCO
DROP TABLE qualityparameter;

DROP TABLE service;


//CRIAR BANCO

CREATE TABLE service(uri VARCHAR(255) PRIMARY KEY, name VARCHAR(512), iscontextservice BOOLEAN,
	location VARCHAR(120), isrollbackable BOOLEAN, iscompactionable BOOLEAN, isdependent BOOLEAN);

CREATE TABLE servicedependencies(dependentserviceuri VARCHAR(255), serviceuri VARCHAR(255),
	PRIMARY KEY(dependentserviceuri, serviceuri) );

alter table servicedependencies add FOREIGN KEY(dependentserviceuri) REFERENCES service(uri);
alter table servicedependencies add FOREIGN KEY(serviceuri) REFERENCES service(uri);

CREATE TABLE qualityparameter(name VARCHAR(255) , serviceuri VARCHAR(255), value VARCHAR(20), impact VARCHAR(20),
	nature VARCHAR(20), lastmeasurement VARCHAR(20), isqocparameter BOOLEAN, PRIMARY KEY(name, serviceuri) );

alter table qualityparameter add  FOREIGN KEY(serviceuri) REFERENCES service(uri);

//inserir serviços

INSERT INTO SERVICE  VALUES('uri://servico1', 's1', true, 'l1', true, false, false);

INSERT INTO SERVICE  VALUES('uri://servico2', 's2', false, 'l2', false, false, false);

INSERT INTO SERVICE  VALUES('uri://servico3', 's3', true, 'l3', false, true, false);

INSERT INTO SERVICE  VALUES('uri://servico4', 's4', false, 'l3', false, false, true);

INSERT INTO SERVICE  VALUES('uri://servico5', 's5', false, 'l1', false, false, false);

INSERT INTO SERVICE  VALUES('uri://servico6', 's6', true, 'l1', false, true, false);

//inserir dependencias
INSERT INTO servicedependencies  VALUES('uri://servico4', 'uri://servico1');
INSERT INTO servicedependencies  VALUES('uri://servico4', 'uri://servico2');


//inserir parametros

//servico1
INSERT INTO qualityparameter VALUES('disponibility','uri://servico1', '2', 'high', 'dynamic', '26/06/2010-10:25:38', false);
INSERT INTO qualityparameter VALUES('performance','uri://servico1', '1', 'high', 'dynamic', '26/06/2010-10:25:38', false);
INSERT INTO qualityparameter VALUES('responsing','uri://servico1', '3', 'low', 'dynamic', '26/06/2010-10:25:38', false);
INSERT INTO qualityparameter VALUES('correctness','uri://servico1', '5', 'high', 'static', '26/06/2010-10:25:38', true);
INSERT INTO qualityparameter VALUES('freshness','uri://servico1', '2', 'low', 'dynamic', '26/06/2010-10:25:38', true);
INSERT INTO qualityparameter VALUES('precision','uri://servico1', '3', 'medium', 'static', '26/06/2010-10:25:38', true);
INSERT INTO qualityparameter VALUES('resolution','uri://servico1', '4', 'medium', 'static', '26/06/2010-10:25:38', true);

//servico2
INSERT INTO qualityparameter VALUES('disponibility','uri://servico2', '3', 'high', 'dynamic', '25/06/2010-13:25:40', false);
INSERT INTO qualityparameter VALUES('performance','uri://servico2', '1', 'low', 'dynamic', '25/06/2010-13:25:40', false);
INSERT INTO qualityparameter VALUES('responsing','uri://servico2', '1', 'low', 'dynamic', '25/06/2010-13:25:40', false);

//servico3
INSERT INTO qualityparameter VALUES('disponibility','uri://servico3', '2', 'medium', 'dynamic', '26/06/2010-10:01:38', false);
INSERT INTO qualityparameter VALUES('performance','uri://servico3', '1', 'low', 'dynamic', '26/06/2010-10:01:38', false);
INSERT INTO qualityparameter VALUES('responsing','uri://servico3', '3', 'high', 'dynamic', '26/06/2010-10:01:38', false);
INSERT INTO qualityparameter VALUES('correctness','uri://servico3', '0', 'low', 'static', '26/06/2010-10:01:38', true);
INSERT INTO qualityparameter VALUES('freshness','uri://servico3', '2', 'medium', 'dynamic', '26/06/2010-10:01:38', true);
INSERT INTO qualityparameter VALUES('precision','uri://servico3', '1', 'medium', 'static', '26/06/2010-10:01:38', true);
INSERT INTO qualityparameter VALUES('resolution','uri://servico3', '6', 'high', 'static', '26/06/2010-10:01:38', true);

//servico4
INSERT INTO qualityparameter VALUES('disponibility','uri://servico4', '0', 'low', 'dynamic', '24/06/2010-00:25:38', false);
INSERT INTO qualityparameter VALUES('performance','uri://servico4', '1', 'low', 'dynamic', '24/06/2010-00:25:38', false);
INSERT INTO qualityparameter VALUES('responsing','uri://servico4', '0', 'low', 'dynamic', '24/06/2010-00:25:38', false);

//servico5
INSERT INTO qualityparameter VALUES('disponibility','uri://servico5', '2', 'high', 'dynamic', '26/06/2010-02:25:17', false);
INSERT INTO qualityparameter VALUES('performance','uri://servico5', '3', 'high', 'dynamic', '26/06/2010-02:25:17', false);
INSERT INTO qualityparameter VALUES('responsing','uri://servico5', '3', 'high', 'dynamic', '26/06/2010-02:25:17', false);

//servico6
INSERT INTO qualityparameter VALUES('disponibility','uri://servico6', '2', 'medium', 'dynamic', '27/06/2010-04:29:08', false);
INSERT INTO qualityparameter VALUES('performance','uri://servico6', '2', 'medium', 'dynamic', '27/06/2010-04:29:08', false);
INSERT INTO qualityparameter VALUES('responsing','uri://servico6', '3', 'high', 'dynamic', '27/06/2010-04:29:08', false);
INSERT INTO qualityparameter VALUES('correctness','uri://servico6', '0', 'low', 'static', '27/06/2010-04:29:08', true);
INSERT INTO qualityparameter VALUES('freshness','uri://servico6', '3', 'high', 'dynamic', '27/06/2010-04:29:08', true);
INSERT INTO qualityparameter VALUES('precision','uri://servico6', '1', 'low', 'static', '27/06/2010-04:29:08', true);
INSERT INTO qualityparameter VALUES('resolution','uri://servico6', '3', 'medium', 'static', '27/06/2010-04:29:08', true);






select * from service;

delete from service where location = 'l1';

select * from service;